﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DataBase
/// </summary>
public static class DataBase
{
    static string connStr = String.Format("server={0};user id={1}; password={2}; Allow Zero Datetime=true;" +
    "database=HeimPicks1013; pooling=false;  respect binary flags=false", "HeimPicks1013.db.10040486.hostedresource.com",
    "HeimPicks1013", "Scarecr0w!");

    //static string connStr = String.Format("server={0};user id={1}; password={2}; Allow Zero Datetime=true;" +
    //"database=HeimPicksTest; pooling=false;  respect binary flags=false", "HeimPicksTest.db.10040486.hostedresource.com",
    //"HeimPicksTest", "Scarecr0w!");

    //static MySql.Data.MySqlClient.MySqlConnection mycon = new MySqlConnection(connStr);


    private static DataSet RetreiveDataSet(MySqlCommand cmd)
    {
        MySqlConnection mycon = new MySqlConnection(connStr);
        MySqlDataAdapter adapter = new MySqlDataAdapter();
        DataSet ds = new DataSet();
        cmd.Connection = mycon;

        try
        {

            mycon.Open();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
        }
        finally
        {
            mycon.Close();
        }

        return ds;
    }

    private static int ExecuteNonQuery(MySqlCommand cmd)
    {
        MySqlConnection mycon = new MySqlConnection(connStr);
        DataSet ds = new DataSet();
        cmd.Connection = mycon;
        try
        {
            mycon.Open();
            cmd.ExecuteNonQuery();
        }
        finally
        {
            mycon.Close();
        }

        return (int)cmd.LastInsertedId;
    }

    /// <summary>
    /// Grabs an Item in the specified Table with the specified ID column that matches the specified ID.
    /// </summary>
    /// <param name="Table">The Table to grab info by.</param>
    /// <param name="IDCol">The Column to use for filtering</param>
    /// <param name="ID">The value to filter by.</param>
    /// <returns>Dataset filled with the info specified by the parameters.</returns>
    public static DataSet GrabAllWithID(string Table, string IDCol, string ID)
    {
        
        string sqlStatement = string.Format("Select * From {0} Where {1} = @ID", Table, IDCol);

        MySqlCommand cmd = new MySqlCommand(sqlStatement);

        cmd.Parameters.AddWithValue("@ID", ID);

        return RetreiveDataSet(cmd);
    }

    private static DataSet GetAllFromTable(string table)
    {
        string sqlStatement = string.Format("Select * From {0}", table);

        MySqlCommand cmd = new MySqlCommand(sqlStatement);

        return RetreiveDataSet(cmd);

    }

    public static class Teams
    {
        enum Columns { ID, TeamName, Website, IconLocation }

        #region Public Methods
        public static void InsertTeam(Team team)
        {
            String sqlStatement = "INSERT INTO Teams(TeamName, Website, IconLocation) VALUES (@team, @website, @iconLocation)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@team", team.TeamName);
            cmd.Parameters.AddWithValue("@website", team.Website);
            cmd.Parameters.AddWithValue("@iconLocation", team.IconURL);

            team.ID = ExecuteNonQuery(cmd);
        }

        public static void UpdateTeam(Team team)
        {
            String sqlStatement = "UPDATE Teams SET TeamName = @team, Website = @website, IconLocation = @iconLocation WHERE ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@team", team.TeamName);
            cmd.Parameters.AddWithValue("@website", team.Website);
            cmd.Parameters.AddWithValue("@iconLocation", team.IconURL);
            cmd.Parameters.AddWithValue("@ID", team.ID);

            ExecuteNonQuery(cmd);
        }

        public static void DeleteTeam(Team team)
        {
            String sqlStatement = "Delete From Teams Where ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", team.ID);

            ExecuteNonQuery(cmd);

            team = null;
        }



        public static Team GetTeam(string teamName)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Teams", "TeamName", teamName);
            Team team = null;
            if (ds.Tables[0].Rows.Count > 0)
                team = MakeTeamFromRow(ds.Tables[0].Rows[0]);
            return team;
        }

        public static Team GetTeamWithID(string ID)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Teams", "ID", ID);
            Team team = null;
            if (ds.Tables[0].Rows.Count > 0)
                team = MakeTeamFromRow(ds.Tables[0].Rows[0]);
            return team;
        }

        public static List<Team> GetAllTeams()
        {
            List<Team> teams = new List<Team>();
            DataSet ds = GetAllTeamsDataSet();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                teams.Add(MakeTeamFromRow(dr));
            }

            return teams;
        }
        #endregion


        #region Private Methods
        private static Team MakeTeamFromRow(DataRow dr)
        {
            Team team = new Team
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                TeamName = dr[(int)Columns.TeamName].ToString(),
                Website = dr[(int)Columns.Website].ToString(),
                IconURL = dr[(int)Columns.IconLocation].ToString()
            };

            return team;
        }

        private static DataSet GetAllTeamsDataSet()
        {
            return GetAllFromTable("Teams");
        }



        #endregion

        #region Tests

        public static bool Test(out string reason)
        {
            bool result = true;
            reason = null;

            Team team = new Team { TeamName = "Andrew's Test", IconURL = "TEST", Website = "Test" };
            InsertTeam(team);
            if (GetTeam(team.TeamName) == null)
            {
                result = false;
                reason += "Insert or Get Fail. ";
            }

            team.TeamName = "Andrew Test2";
            UpdateTeam(team);

            if (GetTeam(team.TeamName) == null)
            {
                result = false;
                reason += "Update Fail. ";
            }

            string teamName = team.TeamName;

            DeleteTeam(team);

            if (GetTeam(teamName) != null)
            {
                result = false;
                reason += "Delete Fail. ";
            }

            return result;
        }

        #endregion
    }

    public static class Users
    {

        enum Columns { ID, UserName, Password, NickName, Slogan, BannerURL, IconURL, ChangePW, Admin }
        public static void InsertUser(User user)
        {
            String sqlStatement = "INSERT INTO Users(UserName, Password, NickName, Slogan, BannerURL, IconURL, Admin)" +
                                   "VALUES (@userName, @password, @nickName, @slogan, @bannerURL, @iconURL, @admin)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@userName", user.UserName);
            cmd.Parameters.AddWithValue("@nickName", user.NickName);
            cmd.Parameters.AddWithValue("@slogan", user.Slogan);
            cmd.Parameters.AddWithValue("@bannerURL", user.BannerURL);
            cmd.Parameters.AddWithValue("@iconURL", user.IconURL);
            cmd.Parameters.AddWithValue("@admin", user.Admin);


            ExecuteNonQuery(cmd);

            user.ID = ExecuteNonQuery(cmd);
        }

        public static void UpdateUser(User user)
        {

            String sqlStatement = "UPDATE Users SET UserName = @userName, NickName = @nickName, " +
                                   "Slogan = @slogan, BannerURL = @bannerURL, IconURL = @iconURL, ChangePW = @changePW, Admin = @admin WHERE ID = @ID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@userName", user.UserName);       
            cmd.Parameters.AddWithValue("@nickName", user.NickName);
            cmd.Parameters.AddWithValue("@slogan", user.Slogan);
            cmd.Parameters.AddWithValue("@bannerURL", user.BannerURL);
            cmd.Parameters.AddWithValue("@iconURL", user.IconURL);
            cmd.Parameters.AddWithValue("@changePW", user.ChangePW);
            cmd.Parameters.AddWithValue("@admin", user.Admin);

            cmd.Parameters.AddWithValue("@ID", user.ID);



            ExecuteNonQuery(cmd);     
        }

        public static void DeleteUser(User user)
        {
            String sqlStatement = "Delete From Users Where ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", user.ID);

            ExecuteNonQuery(cmd);     

            user = null;
        }

        public static User GetUser(string userName)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Users", "Lower(UserName)", userName.ToLower());
            User user = null;
            if (ds.Tables[0].Rows.Count > 0)
                user = MakeUserFromRow(ds.Tables[0].Rows[0]);
            return user;
        }

        public static int UpdatePassword(int userID, string oldPassword, string newPassword)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select Password From Users Where ID = @id");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@id", userID);

            ds = RetreiveDataSet(cmd);

            string hash = ds.Tables[0].Rows[0][0].ToString();


            return -1;
        }


        public static int VerifyUser(string userName, string password)
        {


            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select ID, Password From Users Where UserName = @username");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@username", userName);

            ds = RetreiveDataSet(cmd);

            if (ds.Tables[0].Rows.Count == 0)
                return -1;
            else
            {
                //string hash = ds.Tables[0].Rows[0][1].ToString();
                //if (BCrypt.Net.BCrypt.Verify(password,hash))
                //    return int.Parse(ds.Tables[0].Rows[0][0].ToString());
                //else
                    return 0;
            } 

            
        }


        public static User GetUserWithID(string ID)
        {
            DataSet ds = new DataSet();
            ds = GrabAllWithID("Users", "ID", ID);
            User user = null;
            if (ds.Tables[0].Rows.Count > 0)
                user = MakeUserFromRow(ds.Tables[0].Rows[0]);
            return user;
        }

        public static List<User> GetAllUsers()
        {
            List<User> users = new List<User>();
            DataSet ds = GetAllUsersDataSet();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                users.Add(MakeUserFromRow(dr));
            }

            return users;
        }

        private static User MakeUserFromRow(DataRow dr)
        {
            User user = new User
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                UserName = dr[(int)Columns.UserName].ToString(),
                NickName = dr[(int)Columns.NickName].ToString(),
                Slogan = dr[(int)Columns.Slogan].ToString(),
                BannerURL = dr[(int)Columns.BannerURL].ToString(),
                IconURL = dr[(int)Columns.IconURL].ToString(),
                ChangePW = (bool)(dr[(int)Columns.ChangePW]),
                Admin = (bool)(dr[(int)Columns.Admin])
            };

            return user;
        }

        private static DataSet GetAllUsersDataSet()
        {
            return GetAllFromTable("Users");
        }

        public static bool Test(out string reason)
        {
            bool result = true;
            reason = null;

            User user = new User { UserName = "Andrew", NickName = "Da Man", Slogan = "Bite Me"};

            InsertUser(user);
            if (GetUserWithID(user.ID.ToString()) == null)
            {
                result = false;
                reason += "Insert or Get Fail. ";
            }

            user.UserName = "Andrew2";

            UpdateUser(user);

            if (GetUserWithID(user.ID.ToString()) == null)
            {
                result = false;
                reason += "Update Fail. ";
            }

            string id = user.ID.ToString();

            DeleteUser(user);

            if (GetUserWithID(id) != null)
            {
                result = false;
                reason += "Delete Fail. ";
            }

            return result;
        }
    }

    public static class Games
    {
        enum Columns {ID, HomeTeamID, AwayTeamID, Date, HomeScore, AwayScore, State, WeekID, WinningTeamID, LosingTeamID, HasSpread, hTeamID, hTeamName, hTeamWebsite, hTeamIcon, aTeamID, aTeamName, aTeamWebsite, aTeamIcon, wID, wNumber, wStartDate, wEndDate, wSeasonID, wGamesFound,wPostSeason, AwayTeamRecord, HomeTeamRecord}

        public static void InsertGame(Game game)
        {
            String sqlStatement = "INSERT INTO Games (`HomeTeamID`, `AwayTeamID`, `Date`, `HomeScore`, `AwayScore`, `State`, `WeekID`, WinningTeamID, LosingTeamID, HasSpread)" +
                                   "VALUES (@homeTeamID, @awayTeamID, @date, @homeScore, @awayScore, @state, @weekID, @winningTeamID, @losingTeamID, @hasSpread)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            if (game.HomeTeam == null)
                cmd.Parameters.AddWithValue("@homeTeamID", null);
            else
                cmd.Parameters.AddWithValue("@homeTeamID", game.HomeTeam.ID);
            if (game.AwayTeam == null)
                cmd.Parameters.AddWithValue("@awayTeamID", null);
            else
                cmd.Parameters.AddWithValue("@awayTeamID", game.AwayTeam.ID);
            cmd.Parameters.AddWithValue("@date", game.Date);
            cmd.Parameters.AddWithValue("@homeScore", game.HomeScore);
            cmd.Parameters.AddWithValue("@awayScore", game.AwayScore);
            cmd.Parameters.AddWithValue("@state", (int)game.GameState);
            cmd.Parameters.AddWithValue("@winningTeamID", game.WinningTeamID);
            if (game.GameWeek == null)
                cmd.Parameters.AddWithValue("@weekID", null);
            else
                cmd.Parameters.AddWithValue("@weekID", game.GameWeek.ID);
            cmd.Parameters.AddWithValue("@losingTeamID", game.LosingTeamID);
            cmd.Parameters.AddWithValue("@hasSpread", game.HasSpread);


            //string query = cmd.CommandText;
            //foreach (MySqlParameter p in cmd.Parameters)
            //{
            //    if (p.Value == null)
            //        query = query.Replace(p.ParameterName, "NULL");
            //    else
            //        query = query.Replace(p.ParameterName, p.Value.ToString());
            //}

            game.ID = ExecuteNonQuery(cmd);

        }

        public static void UpdateGame(Game game)
        {
            String sqlStatement = "UPDATE Games SET HomeTeamID = @homeTeamID, AwayTeamID = @awayTeamID, Date = @date," +
                                   "HomeScore = @homeScore, AwayScore = @awayScore, State = @state, WeekID = @weekID, " +
                                   "WinningTeamID = @winningTeamID, LosingTeamID = @losingTeamID, HasSpread = @hasSpread  WHERE ID = @ID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            if (game.HomeTeam == null)
                cmd.Parameters.AddWithValue("@homeTeamID", null);
            else
                cmd.Parameters.AddWithValue("@homeTeamID", game.HomeTeam.ID);
            if (game.AwayTeam == null)
                cmd.Parameters.AddWithValue("@awayTeamID", null);
            else
                cmd.Parameters.AddWithValue("@awayTeamID", game.AwayTeam.ID);
            cmd.Parameters.AddWithValue("@date", game.Date);
            cmd.Parameters.AddWithValue("@homeScore", game.HomeScore);
            cmd.Parameters.AddWithValue("@awayScore", game.AwayScore);
            cmd.Parameters.AddWithValue("@state", game.GameState);
            

            if (game.GameWeek == null)
                cmd.Parameters.AddWithValue("@weekID", null);
            else
                cmd.Parameters.AddWithValue("@weekID", game.GameWeek.ID);
            cmd.Parameters.AddWithValue("@ID", game.ID);

            cmd.Parameters.AddWithValue("@winningTeamID", game.WinningTeamID);
            cmd.Parameters.AddWithValue("@losingTeamID", game.LosingTeamID);
            cmd.Parameters.AddWithValue("@hasSpread", game.HasSpread);

            ExecuteNonQuery(cmd);

        }

        public static void DeleteGame(Game game)
        {
            String sqlStatement = "Delete From Games Where ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", game.ID);

            ExecuteNonQuery(cmd);

            game = null;
        }

        public static Game GetGame(Team awayTeam, Team homeTeam, Week week)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select *, Concat((SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.WinningTeamID = G.AwayTeamID), \" - \",(SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.LosingTeamID = G.AwayTeamID)) as AwayTeamRecord, Concat((SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.WinningTeamID = G.HomeTeamID), \" - \",(SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.LosingTeamID = G.HomeTeamID)) as HomeTeamRecord " +
                                                "From Games as G LEFT JOIN Teams as HomeTeam on G.HomeTeamID = HomeTeam.ID LEFT JOIN Teams as AwayTeam on G.AwayTeamID = AwayTeam.ID LEFT JOIN Weeks as W on G.WeekID = W.ID " +
                                                "Where AwayTeamID = @awayTeamID AND HomeTeamID = @homeTeamID AND WeekID = @weekID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@awayTeamID", awayTeam.ID);
            cmd.Parameters.AddWithValue("@homeTeamID", homeTeam.ID);
            cmd.Parameters.AddWithValue("@weekID", week.ID);

            ds = RetreiveDataSet(cmd);
            
            Game game = null;
            if (ds.Tables[0].Rows.Count > 0)
                game = MakeGameFromRow(ds.Tables[0].Rows[0]);

            return game;
        }

        public static List<Game> GetGamesForWeek(Week week)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * , Concat((SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.WinningTeamID = G.AwayTeamID), \" - \",(SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.LosingTeamID = G.AwayTeamID)) as AwayTeamRecord, Concat((SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.WinningTeamID = G.HomeTeamID), \" - \",(SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.LosingTeamID = G.HomeTeamID)) as HomeTeamRecord " +
                                                "From Games as G LEFT JOIN Teams as HomeTeam on G.HomeTeamID = HomeTeam.ID LEFT JOIN Teams as AwayTeam on G.AwayTeamID = AwayTeam.ID LEFT JOIN Weeks as W on G.WeekID = W.ID " +
                                                "Where  W.ID = @weekID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);

            cmd.Parameters.AddWithValue("@weekID", week.ID);

            ds = RetreiveDataSet(cmd);

            List<Game> games = new List<Game>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach(DataRow dr in ds.Tables[0].Rows)
                    games.Add(MakeGameFromRow(dr));
            }

            games = games.OrderBy(x => x.Date).ToList();
            return games;
        }


        public static List<Game> GetGamesForTeam(Team Team, Season season)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = "Select * , Concat((SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.WinningTeamID = G.AwayTeamID), \" - \",(SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.LosingTeamID = G.AwayTeamID)) as AwayTeamRecord, Concat((SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.WinningTeamID = G.HomeTeamID), \" - \",(SELECT Count(*) FROM `Games` Join Weeks on Games.WeekID = Weeks.ID Where Weeks.ID <= W.Number AND Weeks.Season = W.Season AND Games.LosingTeamID = G.HomeTeamID)) as HomeTeamRecord " +
                                                "From Games as G LEFT JOIN Teams as HomeTeam on G.HomeTeamID = HomeTeam.ID LEFT JOIN Teams as AwayTeam on G.AwayTeamID = AwayTeam.ID LEFT JOIN Weeks as W on G.WeekID = W.ID " +
                                                "Where G.HomeTeamID = @teamID AND W.Season = @seasonID OR G.AwayteamID = @teamID AND W.Season = @seasonID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);

            cmd.Parameters.AddWithValue("@teamID", Team.ID);
            cmd.Parameters.AddWithValue("@seasonID", season.ID);

            ds = RetreiveDataSet(cmd);

            List<Game> games = new List<Game>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                    games.Add(MakeGameFromRow(dr));
            }
            return games;
        }

        private static Game MakeGameFromRow(DataRow dr)
        {
            Game game = new Game
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                AwayTeam = new Team
                {
                    ID = int.Parse(dr[(int)Columns.aTeamID].ToString()),
                    TeamName = dr[(int)Columns.aTeamName].ToString(),
                    IconURL = dr[(int)Columns.aTeamIcon].ToString(),
                    Website = dr[(int)Columns.aTeamWebsite].ToString()
                },
                HomeTeam = new Team
                {
                    ID = int.Parse(dr[(int)Columns.hTeamID].ToString()),
                    TeamName = dr[(int)Columns.hTeamName].ToString(),
                    IconURL = dr[(int)Columns.hTeamIcon].ToString(),
                    Website = dr[(int)Columns.hTeamWebsite].ToString()
                },

                HomeScore = int.Parse((dr[(int)Columns.HomeScore].ToString())),
                AwayScore = int.Parse(dr[(int)Columns.AwayScore].ToString()),
                HasSpread = (bool)dr[(int)Columns.HasSpread],
                GameState = (Game.State)int.Parse(dr[(int)Columns.State].ToString()),
                Date = DateTime.Parse(dr[(int)Columns.Date].ToString()),
                GameWeek = new Week
                {
                    ID = int.Parse(dr[(int)Columns.wID].ToString()),
                    Number = int.Parse(dr[(int)Columns.wNumber].ToString()),
                    StartDate = DateTime.Parse(dr[(int)Columns.wStartDate].ToString()),
                    StopDate = DateTime.Parse(dr[(int)Columns.wEndDate].ToString()),
                    GamesFound = (bool)dr[(int)Columns.wGamesFound],
                },
                WinningTeamID = dr[(int)Columns.WinningTeamID].ToString() == "" ? -1 : int.Parse(dr[(int)Columns.WinningTeamID].ToString()),
                LosingTeamID = dr[(int)Columns.LosingTeamID].ToString() == "" ? -1 : int.Parse(dr[(int)Columns.LosingTeamID].ToString()),
                AwayTeamRecord = dr[(int)Columns.AwayTeamRecord].ToString(),
                HomeTeamRecord = dr[(int)Columns.HomeTeamRecord].ToString()
            };



            return game;
        }

        public static Game GetGame(string ID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Games " +
                                                "LEFT JOIN Teams as HomeTeam on Games.HomeTeamID = HomeTeam.ID " +
                                                "LEFT JOIN Teams as AwayTeam on Games.AwayTeamID = AwayTeam.ID " +
                                                "LEFT JOIN Weeks on Games.WeekID = Weeks.ID " +
                                                "Where Games.ID = @ID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", ID);

            ds = RetreiveDataSet(cmd);

            Game game = null;
            if (ds.Tables[0].Rows.Count > 0)
                game = MakeGameFromRow(ds.Tables[0].Rows[0]);
            return game;
        }

        public static bool Test(out string reason)
        {
            bool result = true;
            reason = null;

            Game game = new Game {AwayScore = 0, HomeScore = 0, Date = DateTime.Now, GameState = Game.State.PreGame };
            InsertGame(game);
            if (GetGame(game.ID.ToString()) == null)
            {
                result = false;
                reason += "Insert or Get Fail. ";
            }

            game.GameState = Game.State.InGame;

            UpdateGame(game);
            Game newGame = GetGame(game.ID.ToString());
            if (newGame == null || newGame.GameState != game.GameState)
            {
                result = false;
                reason += "Update Fail. ";
            }

            string id = game.ID.ToString();

            DeleteGame(game);

            if (GetGame(id) != null)
            {
                result = false;
                reason += "Delete Fail. ";
            }

            return result;
        }

    }

    public static class UserPicks
    {
        enum Columns {UserID, UserName, GameID, TeamPickedID, Spread}

        public static void InsertUserPick(UserPick userPick)
        {
            DeleteUserPick(userPick);
            String sqlStatement = "INSERT INTO UserPicks (`UserID`, `GameID`, `ChosenTeamID`)" +
                                "VALUES (@userID, @gameID, @chosenTeamID)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@userID", userPick.UserID);
            cmd.Parameters.AddWithValue("@gameID", userPick.GameID);
            cmd.Parameters.AddWithValue("@chosenTeamID", userPick.ChosenTeamID);


            ExecuteNonQuery(cmd);
        }

        public static void UpdateUserPick(UserPick userPick)
        {
            String sqlStatement = "UPDATE UserPicks SET ChosenTeamID = @chosenTeamID, Spread = @spread" +
                       " WHERE UserID = @userID and GameID = @gameID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@chosenTeamID", userPick.ChosenTeamID);
            cmd.Parameters.AddWithValue("@spread", userPick.Spread);
            cmd.Parameters.AddWithValue("@userID", userPick.UserID);
            cmd.Parameters.AddWithValue("@gameID", userPick.GameID);

            ExecuteNonQuery(cmd);

        }

        public static void DeleteUserPick(UserPick userPick)
        {
            String sqlStatement = "Delete From UserPicks WHERE UserID = @userID and GameID = @gameID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@userID", userPick.UserID);
            cmd.Parameters.AddWithValue("@gameID", userPick.GameID);

            ExecuteNonQuery(cmd);

            userPick = null;
        }

        //public static UserPick GetUserPick(int gameID, int userID)
        //{
        //    MySqlDataAdapter adapter = new MySqlDataAdapter();
        //    DataSet ds = new DataSet();
        //    string sqlStatement = string.Format("Select Users.ID, Users.UserName, UsersPicks.GameID, UsersPicks.ChosenTeamID From UserPicks Left Join Users On UserPicks.UserID = Users.ID WHERE UserID = @userID and GameID = @gameID");

        //    MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
        //    cmd.Parameters.AddWithValue("@userID", userID);
        //    cmd.Parameters.AddWithValue("@gameID", gameID);

        //    mycon.Open();
        //    adapter.SelectCommand = cmd;
        //    adapter.Fill(ds);
        //    mycon.Close();
        //    UserPick userPick = null;
        //    if (ds.Tables[0].Rows.Count > 0)
        //        userPick = MakeUserPickFromRow(ds.Tables[0].Rows[0]);

        //    return userPick;
        //}

        //public static List<UserPick> GetAllUserPicksForGame(Game game)
        //{
        //    DataSet ds = new DataSet();
        //    ds = GrabAllWithID("UserPicks", "GameID", game.ID.ToString());
        //    List<UserPick> userPicks = new List<UserPick>();
        //    if (ds.Tables[0].Rows.Count > 0)
        //    {
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //            userPicks.Add(MakeUserPickFromRow(dr));
        //    }
        //    return userPicks;
        //}

        //public static List<UserPick> GetAllUserPicksForWeekAndUser(User user, Week week)
        //{
        //    MySqlDataAdapter adapter = new MySqlDataAdapter();
        //    DataSet ds = new DataSet();
        //    string sqlStatement = string.Format("Select Users.ID, Users.UserName, UsersPicks.GameID, UsersPicks.ChosenTeamID From UserPicks Left Join Users On UserPicks.UserID = Users.ID From UserPicks INNER JOIN Games ON UserPicks.GameID = Games.ID  WHERE UserPicks.UserID = @userID and Games.WeekID = @weekID");

        //    MySqlCommand cmd = new MySqlCommand(sqlStatement, mycon);
        //    cmd.Parameters.AddWithValue("@userID", user.ID);
        //    cmd.Parameters.AddWithValue("@week", week.ID);

        //    mycon.Open();
        //    adapter.SelectCommand = cmd;
        //    adapter.Fill(ds);
        //    mycon.Close();
        //    List<UserPick> userPicks = new List<UserPick>();
        //    if (ds.Tables[0].Rows.Count > 0)
        //    {
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //            userPicks.Add(MakeUserPickFromRow(dr));
        //    }
        //    return userPicks;
        //}

        public static List<UserPick> GetAllUserPicksForWeek(Week week)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select Users.ID, Users.UserName, UserPicks.GameID, UserPicks.ChosenTeamID, UserPicks.Spread From UserPicks Left Join Users On UserPicks.UserID = Users.ID INNER JOIN Games ON UserPicks.GameID = Games.ID  WHERE Games.WeekID = @weekID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@weekID", week.ID);

            ds = RetreiveDataSet(cmd);

            List<UserPick> userPicks = new List<UserPick>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                    userPicks.Add(MakeUserPickFromRow(dr));
            }
            return userPicks;
        }

        public static List<UserPick> GetAllUserPicksForTeam(Team team)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select Users.ID, Users.UserName, UserPicks.GameID, UserPicks.ChosenTeamID, UserPicks.Spread From UserPicks Left Join Users On UserPicks.UserID = Users.ID INNER JOIN Games ON UserPicks.GameID = Games.ID  WHERE Games.HomeTeamID = @teamID OR Games.AwayTeamID = @teamID");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@teamID", team.ID);

            ds = RetreiveDataSet(cmd);

            List<UserPick> userPicks = new List<UserPick>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                    userPicks.Add(MakeUserPickFromRow(dr));
            }
            return userPicks;
        }

        public static UserPick MakeUserPickFromRow(DataRow dr)
        {
            UserPick userPick = new UserPick
            {
                UserID = int.Parse(dr[(int)Columns.UserID].ToString()),
                UserName = dr[(int)Columns.UserName].ToString(),
                GameID= int.Parse(dr[(int)Columns.GameID].ToString()),
                ChosenTeamID = int.Parse(dr[(int)Columns.TeamPickedID].ToString()),
                Spread= int.Parse(dr[(int)Columns.Spread].ToString())
            };

            return userPick;
        }




        
    }

    public static class Weeks
    {
        enum Columns {ID, Number, StartDate, EndDate, Season, GamesFound, PostSeason}

        public static void InsertWeek(Week week)
        {
            String sqlStatement = "INSERT INTO Weeks (`Number`, `StartDate`, `EndDate`, `Season`, `GamesFound`, `PostSeason`)" +
                                "VALUES (@number, @startDate, @endDate, @season, @gameFound, @postSeason)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@number", week.Number);
            cmd.Parameters.AddWithValue("@startDate", ((DateTime)week.StartDate).ToString("yyyy-MM-dd"));
            cmd.Parameters.AddWithValue("@endDate", ((DateTime)week.StopDate).ToString("yyyy-MM-dd"));
            if(week.WeekSeason == null)
                cmd.Parameters.AddWithValue("@season", null);
            else
                cmd.Parameters.AddWithValue("@season", week.WeekSeason.ID);
            cmd.Parameters.AddWithValue("@gameFound", week.GamesFound);
            cmd.Parameters.AddWithValue("@postSeason", week.PostSeason);

            week.ID = ExecuteNonQuery(cmd);
        }

        public static void DeclareWinner(string WeekID, string UserID, bool BySpread)
        {
            String sqlStatement = "INSERT INTO Winners (`WeekID`, `UserID`, `WonBySpread`)" +
                                "VALUES (@weekID, @userID, @bySpread)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@weekID", WeekID);
            cmd.Parameters.AddWithValue("@userID", UserID);
            cmd.Parameters.AddWithValue("@bySpread", BySpread);

            ExecuteNonQuery(cmd);
        }

        public static void UpdateWeek(Week week)
        {
            String sqlStatement = "UPDATE Weeks SET Number = @number, StartDate = @startDate, EndDate = @endDate, Season = @season, GamesFound = @gameFound, PostSeason = @postSeason " +
                       " WHERE ID = @ID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@number", week.Number);
            cmd.Parameters.AddWithValue("@startDate", ((DateTime)week.StartDate).ToString("yyyy-MM-dd"));
            cmd.Parameters.AddWithValue("@endDate", ((DateTime)week.StopDate).ToString("yyyy-MM-dd"));
            if (week.WeekSeason == null)
                cmd.Parameters.AddWithValue("@season", null);
            else
                cmd.Parameters.AddWithValue("@season", week.WeekSeason.ID);
            int i = week.GamesFound ? 1 : 0;
            cmd.Parameters.AddWithValue("@gameFound", i);
            cmd.Parameters.AddWithValue("@postSeason", week.PostSeason);

            cmd.Parameters.AddWithValue("@ID", week.ID);

            ExecuteNonQuery(cmd);
        }

        public static void DeleteWeek(Week week)
        {
            String sqlStatement = "Delete From Weeks WHERE ID = @ID" ;
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", week.ID);
            ExecuteNonQuery(cmd);

            week = null;
        }


        public static Week GetWeek(string number, Season season)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Weeks WHERE Number = @number and Season = @season");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@number", number);
            cmd.Parameters.AddWithValue("@season", season.ID);

            ds = RetreiveDataSet(cmd);


            Week week = null;
            if (ds.Tables[0].Rows.Count > 0)
                week = MakeWeekFromRow(ds.Tables[0].Rows[0]);

            return week;
        }

        public static Week GetSeasonsCurrentWeek(string seasonID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Weeks WHERE Season = @season");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@season", seasonID);

            ds = RetreiveDataSet(cmd);


            Week week = null;
            if (ds.Tables[0].Rows.Count > 0)
                week = MakeWeekFromRow(ds.Tables[0].Rows[0]);

            return week;
        }

        public static Week GetWeekWithID(string weekID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Weeks WHERE ID = @ID ");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", weekID);

            ds = RetreiveDataSet(cmd);


            Week week = null;
            if (ds.Tables[0].Rows.Count > 0)
                week = MakeWeekFromRow(ds.Tables[0].Rows[0]);

            return week;
        }

      

        public static int GetNumberOfGamesLeft(string weekID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select Count(*) From Games Where WeekID = @weekID and (WinningTeamID is null OR WinningTeamID = -1 ) ");
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@weekID", weekID);

            ds = RetreiveDataSet(cmd);


            int i = int.Parse(ds.Tables[0].Rows[0][0].ToString());

            return i;
        }

        public static Week GetCurrentWeek()
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            var currentSeason = Seasons.GetCurrentSeason();
            string sqlStatement = string.Format("SELECT * FROM `Weeks` WHERE  ADDDATE( EndDate, 1 )  >= Now() && Season = @seasonID ORDER BY StartDate");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@seasonID", currentSeason.ID);

            ds = RetreiveDataSet(cmd);


            Week week = null;
            if (ds.Tables[0].Rows.Count > 0)
                week = MakeWeekFromRow(ds.Tables[0].Rows[0]);

            return week;
        }


        public static int GetWeekSpread(string weekID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("SELECT (HomeScore + AwayScore) FROM `Games` WHERE WeekID = @weekID And HasSpread = true");
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@weekID", weekID);

            ds = RetreiveDataSet(cmd);


            int i = int.Parse(ds.Tables[0].Rows[0][0].ToString());

            return i;

        }

        public static int GetUsersWeekSpreadGuess(string weekID,string userID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("SELECT Spread, Games.ID FROM UserPicks  Join Games on UserPicks.GameID = Games.ID WHERE Games.WeekID = @weekID And Games.HasSpread = true and UserID = @userID ");
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@weekID", weekID);
            cmd.Parameters.AddWithValue("@userID", userID);

            ds = RetreiveDataSet(cmd);

            int i = int.Parse(ds.Tables[0].Rows[0][0].ToString());

            return i;

        }
        private static Week  MakeWeekFromRow(DataRow dr)
        {
            Week week = new Week
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                Number = int.Parse(dr[(int)Columns.Number].ToString()),
                StartDate = DateTime.Parse(dr[(int)Columns.StartDate].ToString()),
                StopDate = DateTime.Parse(dr[(int)Columns.EndDate].ToString()),
                WeekSeason = Seasons.GetSeasonWithID(dr[(int)Columns.Season].ToString()),
                GamesFound = (bool)dr[(int)Columns.GamesFound],
                PostSeason = (bool)dr[(int)Columns.PostSeason]
            };


            return week;
        }

        public static List<Week> GetWeeks(Season season)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Weeks " +
                            
                                                "WHERE Season = @ID ");
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", season.ID);

            ds = RetreiveDataSet(cmd);

            List<Week> weeks = new List<Week>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                    weeks.Add(MakeWeekFromRow(dr));
            }
            return weeks;
        }

        public static bool Test(out string reason)
        {
            bool result = true;
            reason = null;

            Week week = new Week { Number = 1, StartDate = DateTime.Now, StopDate = DateTime.Now, GamesFound = false };

             InsertWeek(week);

            if (GetWeekWithID(week.ID.ToString()) == null)
            {
                result = false;
                reason += "Insert or Get Fail. ";
            }

            week.GamesFound = true;

            UpdateWeek(week);
            Week newWeek =  GetWeekWithID(week.ID.ToString());

            if (newWeek  == null || newWeek.GamesFound != week.GamesFound)
            {
                result = false;
                reason += "Update Fail. ";
            }

            string id = week.ID.ToString();

            DeleteWeek(week);

            if (GetWeekWithID(id) != null)
            {
                result = false;
                reason += "Delete Fail. ";
            }

            return result;
        }



        public static int GetSeasonsWeekCount(Season season)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select Count(*) From Weeks " +

                                                "WHERE Season = @ID ");
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", season.ID);

            ds = RetreiveDataSet(cmd);

            int count = 0;
            if (ds.Tables[0].Rows.Count > 0)
                count = int.Parse(ds.Tables[0].Rows[0][0].ToString());

            return count;
        }
    }

    public static class Seasons
    {
        enum Columns { ID, StartYear, EndYear, CurrentSeason };

        public static void InsertSeason(Season season)
        {
            String sqlStatement = "INSERT INTO Seasons (`StartYear`, `Endyear`, `CurrentSeason`)" +
                            "VALUES (@startYear, @endYear, @currentSeason)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@startYear", season.StartYear);
            cmd.Parameters.AddWithValue("@endYear", season.EndYear);
            cmd.Parameters.AddWithValue("@currentSeason", season.IsCurrentSeason);
            cmd.Parameters.AddWithValue("@ID", season.ID);

            season.ID = ExecuteNonQuery(cmd);
        }

        public static void UpdateSeason(Season season)
        {
            String sqlStatement = "UPDATE Seasons SET StartYear = @startYear, Endyear = @endYear, CurrentSeason = @currentSeason" +
                " WHERE ID = @ID";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@startYear", season.StartYear);
            cmd.Parameters.AddWithValue("@endYear", season.EndYear);
            cmd.Parameters.AddWithValue("@currentSeason", season.IsCurrentSeason);
            cmd.Parameters.AddWithValue("@ID", season.ID);
            ExecuteNonQuery(cmd);
        }

        public static void DeleteSeason(Season season)
        {
            String sqlStatement = "Delete From Seasons WHERE ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", season.ID);

            ExecuteNonQuery(cmd);

            season = null;
        }

        public static Season GetCurrentSeason()
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Seasons WHERE CurrentSeason = true ");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);

            ds = RetreiveDataSet(cmd);
            Season season = null;
            if (ds.Tables[0].Rows.Count > 0)
                season = MakeSeasonFromRow(ds.Tables[0].Rows[0]);

            return season;
        }

        public static Season GetSeasonWithID(string ID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Seasons WHERE ID = @ID ");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", ID);

            ds = RetreiveDataSet(cmd);

            Season season = null;
            if (ds.Tables[0].Rows.Count > 0)
                season = MakeSeasonFromRow(ds.Tables[0].Rows[0]);

            return season;
        }

        public static DataSet GetSeasonStandingsByWeeksWon(string seasonID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("SELECT Distinct U.NickName,  (Select Count(*) FROM Winners as Win Left Join Weeks as W on W.ID = Win.WeekID Where Win.UserID = U.ID AND W.Season = @seasonID) as WeeksWon From Users as U Order By WeeksWon Desc");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@seasonID", seasonID);

            ds = RetreiveDataSet(cmd);

            return ds;
        }

        public static DataSet GetSeasonStandingsByGames(string seasonID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("SELECT Distinct Us.NickName, (Select Count(*) From Games as G Left Join UserPicks as UP on G.ID = UP.GameID JOIN Users as U on UP.UserID = U.ID JOIN Weeks as W on W.ID = G.WeekID JOIN Seasons as S on S.ID = W.Season Where S.ID = @seasonID AND G.State = 2 AND G.WinningTeamID = UP.ChosenTeamID AND UP.UserID = Us.ID) as Wins, (Select Count(*) From Games as G Left Join UserPicks as UP on G.ID = UP.GameID JOIN Users as U on UP.UserID = U.ID JOIN Weeks as W on W.ID = G.WeekID JOIN Seasons as S on S.ID = W.Season Where S.ID = @seasonID AND G.State = 2 AND G.WinningTeamID <> UP.ChosenTeamID AND UP.UserID = Us.ID) as Losses From Games as G Left Join UserPicks as UP on G.ID = UP.GameID JOIN Users as Us on UP.UserID = Us.ID JOIN Weeks as W on W.ID = G.WeekID JOIN Seasons as S on S.ID = W.Season Where S.ID = @seasonID Order by Wins DESC");
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@seasonID", seasonID);

            ds = RetreiveDataSet(cmd);


            return ds;
        }

        private static Season MakeSeasonFromRow(DataRow dr)
        {
            Season season = new Season
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                StartYear = int.Parse(dr[(int)Columns.StartYear].ToString()),
                EndYear = int.Parse(dr[(int)Columns.EndYear].ToString()),
                IsCurrentSeason = (bool)dr[(int)Columns.CurrentSeason]
            };

            return season;
        }

        public static bool Test(out string reason)
        {
            bool result = true;
            reason = null;

            Season season = new Season { StartYear = 2011, EndYear = 2012, IsCurrentSeason = true };

            InsertSeason(season);

            if (GetSeasonWithID(season.ID.ToString()) == null)
            {
                result = false;
                reason += "Insert or Get Fail. ";
            }

            season.IsCurrentSeason = false;

            UpdateSeason(season);
            Season newSeason = GetSeasonWithID(season.ID.ToString());

            if (newSeason == null || newSeason.IsCurrentSeason != season.IsCurrentSeason)
            {
                result = false;
                reason += "Update Fail. ";
            }

            string id = season.ID.ToString();

            DeleteSeason(season);

            if (GetSeasonWithID(id) != null)
            {
                result = false;
                reason += "Delete Fail. ";
            }

            return result;
        }

        public static List<Season> GetSeasons()
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("Select * From Seasons ORDER BY StartYear DESC");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);

            ds = RetreiveDataSet(cmd);

            List<Season> seasons = new List<Season>();;
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach(DataRow r in ds.Tables[0].Rows)
                seasons.Add(MakeSeasonFromRow(r));
            }

            return seasons;
        }
    }

    public static class Rankings
    {
        enum Columns {UserID, UserName, Wins, Losses, Winner, WonBySpread};
        public static List<UserRanking> GetWeekStandings(string weekID)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("SELECT Distinct Us.ID, Us.NickName, (Select Count(*) From Games as G Left Join UserPicks as UP on G.ID = UP.GameID JOIN Users as U on UP.UserID = U.ID Where G.WeekID = @weekID AND G.State = 2 AND G.WinningTeamID = UP.ChosenTeamID AND UP.UserID = Us.ID) as Wins, (Select Count(*) From Games as G Left Join UserPicks as UP on G.ID = UP.GameID JOIN Users as U on UP.UserID = U.ID Where G.WeekID = @weekID AND G.State = 2 AND G.WinningTeamID <> UP.ChosenTeamID AND UP.UserID = Us.ID) as Losses, (Select Count(*) From Winners Where WeekID = @weekID AND UserID = Us.ID) > 0 as Winner,(Select Count(*) From Winners Where WeekID = @weekID AND UserID = Us.ID And WonBySpread = True) > 0 as WonBySpread From Games as G Left Join UserPicks as UP on G.ID = UP.GameID JOIN Users as Us on UP.UserID = Us.ID Where G.WeekID = @weekID Order by Wins DESC");
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@weekID", weekID);

            ds = RetreiveDataSet(cmd);

            List<UserRanking> rankings = new List<UserRanking>();
            UserRanking lastRanking = null;
            int currentPlace = 1;

            foreach (DataRow r in ds.Tables[0].Rows)
            {
                UserRanking uR = MakeRankingFromRow(r);

                if(lastRanking != null)
                {
                    if(lastRanking.Wins > uR.Wins)
                        currentPlace++;
                }
                lastRanking = uR;
                uR.Place = currentPlace;

                rankings.Add(uR);
            }

            return rankings;
        }


   private static UserRanking MakeRankingFromRow(DataRow dr)
    {
        UserRanking ranking = new UserRanking
        {
            UserID = int.Parse(dr[(int)Columns.UserID].ToString()),
            UserName = dr[(int)Columns.UserName].ToString(),
            Wins = int.Parse(dr[(int)Columns.Wins].ToString()),
            Losses = int.Parse(dr[(int)Columns.Losses].ToString()),
            Winner = ((int)dr[(int)Columns.Winner]) == 1?true :false,
            WinnerBySpread = ((int)dr[(int)Columns.WonBySpread]) == 1 ? true : false
        };


        return ranking;
    }

        public static DataSet GetRankingsByWeeksWon()
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("SELECT Distinct U.NickName,  (Select Count(*) FROM Winners  Where Winners.UserID = U.ID) as WeeksWon From Users as U Order By WeeksWon Desc");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);

            ds = RetreiveDataSet(cmd);



            return ds;
        }
    }

    public static class Messages
    {
        enum Columns { ID, User, Week, Date, Message, UserNickName, Computer, Style };

        public static void InsertMessage(Message message)
        {
            String sqlStatement = "INSERT INTO Messages (`User`, `Week`, `Date`, `Message`, `Computer`, `Style`)" +
                            "VALUES (@userID, @weekID, @date, @messge, @computer, @style)";

            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@userID",  message.UserID);
            cmd.Parameters.AddWithValue("@weekID", message.WeekID);
            cmd.Parameters.AddWithValue("@date",  message.Date);
            cmd.Parameters.AddWithValue("@messge", message.Note);
            cmd.Parameters.AddWithValue("@computer", message.Computer);
            cmd.Parameters.AddWithValue("@style", message.Style);


            message.ID = ExecuteNonQuery(cmd);
        }


        public static void DeleteMessage(Message message)
        {
            String sqlStatement = "Delete From Messages WHERE ID = @ID";
            MySqlCommand cmd = new MySqlCommand(sqlStatement);
            cmd.Parameters.AddWithValue("@ID", message.ID);

            ExecuteNonQuery(cmd);

            message = null;
        }

        public static List<Message> GetWeekMessages(Week week)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            DataSet ds = new DataSet();
            string sqlStatement = string.Format("SELECT M.ID, M.User, M.Week, M.Date, M.Message, U.NickName, M.Computer, M.Style  FROM Messages M JOIN Users U on M.User = U.ID  WHERE Week = @weekID Order By M.Date");

            MySqlCommand cmd = new MySqlCommand(sqlStatement);

            cmd.Parameters.AddWithValue("@weekID", week.ID);

            ds = RetreiveDataSet(cmd);
            List<Message> messages = new List<Message>();

            foreach (DataRow r in ds.Tables[0].Rows)
                messages.Add(MakeMessageFromRow(r));

            return messages;
        }

        private static Message MakeMessageFromRow(DataRow dr)
        {
            Message message = new Message
            {
                ID = int.Parse(dr[(int)Columns.ID].ToString()),
                UserID = int.Parse(dr[(int)Columns.User].ToString()),
                Date = DateTime.Parse(dr[(int)Columns.Date].ToString()),
                WeekID = int.Parse(dr[(int)Columns.Week].ToString()),
                Note = dr[(int)Columns.Message].ToString(),
                UserNickName = dr[(int)Columns.UserNickName].ToString(),
                Computer = (bool)dr[(int)Columns.Computer],
                Style = dr[(int)Columns.Style].ToString(),


            };

            return message;
        }

        public static bool Test(out string reason)
        {
            
            //bool result = true;
            reason = null;

            //Season season = new Season { StartYear = 2011, EndYear = 2012, IsCurrentSeason = true };

            //InsertSeason(season);

            //if (GetSeasonWithID(season.ID.ToString()) == null)
            //{
            //    result = false;
            //    reason += "Insert or Get Fail. ";
            //}

            //season.IsCurrentSeason = false;

            //UpdateSeason(season);
            //Season newSeason = GetSeasonWithID(season.ID.ToString());

            //if (newSeason == null || newSeason.IsCurrentSeason != season.IsCurrentSeason)
            //{
            //    result = false;
            //    reason += "Update Fail. ";
            //}

            //string id = season.ID.ToString();

            //DeleteSeason(season);

            //if (GetSeasonWithID(id) != null)
            //{
            //    result = false;
            //    reason += "Delete Fail. ";
            //}

            return true;
        }


    }
}